﻿
Public Class ThisWorkbook

    ' This is used to prevent the 'save changes prompt' from being presented to the user if data hasn't
    ' been imported when the user chooses to close the Excel application.
    Private _silence As Boolean = False

    ' for internal use... exposed as Friend properties
    Private managerEmployeeID_backing As Integer = 0
    Private companyIDs_backing As System.Collections.Generic.Dictionary(Of String, Integer) = New System.Collections.Generic.Dictionary(Of String, Integer)
    Private salesContactEmail_backing As String = ""
    Private managerContactEmail_backing As String = ""
    Private advWorksDC_backing As SalesForecastWorkbook.AdventureWorksVSTO
    Private salesDataTable_backing As SalesData.DataTable1DataTable = New SalesData.DataTable1DataTable()
    Private blankDataTable_backing As SalesData.DataTable1DataTable = New SalesData.DataTable1DataTable()
    Private forecastDataTable_backing As SalesData.DataTable1DataTable = New SalesData.DataTable1DataTable()
    Private actionsPaneForecast_backing As ActionsPaneForecast = New ActionsPaneForecast()
    Private salesStartDate_backing As Date
    Private salesEndDate_backing As Date

    ''' <summary>
    ''' Is the taskpane currently visible
    ''' </summary>
    ''' <value></value>
    ''' <returns>True if the taskpane is visibble, or allows you to set the visible status</returns>
    ''' <remarks></remarks>
    Public Property TaskPaneVisible() As Boolean
        Get
            Return Application.CommandBars("Task Pane").Visible
        End Get
        Set(ByVal value As Boolean)
            Application.CommandBars("Task Pane").Visible = value
            Globals.Ribbons.Ribbon1.IsTaskPaneVisible = value
        End Set
    End Property

#Region "Cached Properties"

    Private salesEmployeeID_backing As Integer = 0
    Private salesContactID_backing As Integer = 0
    Private territoryID_backing As Integer = 0
    Private forecastDate_backing As Date = Today
    Private forecastID_backing As Integer = 0
    Private forecastStartYear_backing As Integer = 2009
    Private forecastStartMonth_backing As Integer = 4

    <Cached()> _
    Public Property SalesEmployeeID() As Integer
        Get
            Return salesEmployeeID_backing
        End Get
        Set(ByVal Value As Integer)
            salesEmployeeID_backing = Value
        End Set
    End Property

    <Cached()> _
    Public Property SalesContactID() As Integer
        Get
            Return salesContactID_backing
        End Get
        Set(ByVal Value As Integer)
            salesContactID_backing = Value
        End Set
    End Property

    <Cached()> _
    Public Property TerritoryID() As Integer
        Get
            Return territoryID_backing
        End Get
        Set(ByVal Value As Integer)
            territoryID_backing = Value
        End Set
    End Property

    <Cached()> _
    Public Property ForecastDate() As Date
        Get
            Return forecastDate_backing
        End Get
        Set(ByVal Value As Date)
            forecastDate_backing = Value
        End Set
    End Property

    <Cached()> _
    Public Property ForecastID() As Integer
        Get
            Return forecastID_backing
        End Get
        Set(ByVal Value As Integer)
            forecastID_backing = Value
        End Set
    End Property

    <Cached()> _
    Public Property ForecastStartYear() As Integer
        Get
            Return forecastStartYear_backing
        End Get
        Set(ByVal Value As Integer)
            forecastStartYear_backing = Value
        End Set
    End Property

    <Cached()> _
    Public Property ForecastStartMonth() As Integer
        Get
            Return forecastStartMonth_backing
        End Get
        Set(ByVal Value As Integer)
            forecastStartMonth_backing = Value
        End Set
    End Property

#End Region

    Friend Property ManagerEmployeeID() As Integer
        Get
            Return managerEmployeeID_backing
        End Get
        Set(ByVal Value As Integer)
            managerEmployeeID_backing = Value
        End Set
    End Property

    Friend Property CompanyIDs() As System.Collections.Generic.Dictionary(Of String, Integer)
        Get
            Return CompanyIDs_backing
        End Get
        Set(ByVal value As System.Collections.Generic.Dictionary(Of String, Integer))
            CompanyIDs_backing = value
        End Set
    End Property

    Friend Property SalesContactEmail() As String
        Get
            Return SalesContactEmail_backing
        End Get
        Set(ByVal Value As String)
            SalesContactEmail_backing = Value
        End Set
    End Property

    Friend Property ManagerContactEmail() As String
        Get
            Return managerContactEmail_backing
        End Get
        Set(ByVal Value As String)
            managerContactEmail_backing = Value
        End Set
    End Property

    Friend Property AdvWorksDC() As SalesForecastWorkbook.AdventureWorksVSTO
        Get
            Return AdvWorksDC_backing
        End Get
        Set(ByVal Value As SalesForecastWorkbook.AdventureWorksVSTO)
            AdvWorksDC_backing = Value
        End Set
    End Property

    Friend Property SalesDataTable() As SalesData.DataTable1DataTable
        Get
            Return salesDataTable_backing
        End Get
        Set(ByVal Value As SalesData.DataTable1DataTable)
            salesDataTable_backing = Value
        End Set
    End Property

    Friend Property BlankDataTable() As SalesData.DataTable1DataTable
        Get
            Return blankDataTable_backing
        End Get
        Set(ByVal Value As SalesData.DataTable1DataTable)
            blankDataTable_backing = Value
        End Set
    End Property

    Friend Property ForecastDataTable() As SalesData.DataTable1DataTable
        Get
            Return ForecastDataTable_backing
        End Get
        Set(ByVal Value As SalesData.DataTable1DataTable)
            ForecastDataTable_backing = Value
        End Set
    End Property

    Public Property ActionsPaneForecast1() As ActionsPaneForecast
        Get
            Return ActionsPaneForecast_backing
        End Get
        Set(ByVal Value As ActionsPaneForecast)
            ActionsPaneForecast_backing = Value
        End Set
    End Property
    ' Get the current sales data.
    ' start with the previous month and take the 12 previous months - for demonstration purposes
    ' this is the preferable solution since the various charts (Quarterly and Monthly) will align 
    ' ex: 2/08 means take 4/07 though 3/08
    Friend Property SalesStartDate() As Date
        Get
            Return SalesStartDate_backing
        End Get
        Set(ByVal Value As Date)
            SalesStartDate_backing = Value
        End Set
    End Property

    Friend Property SalesEndDate() As Date
        Get
            Return SalesEndDate_backing
        End Get
        Set(ByVal Value As Date)
            SalesEndDate_backing = Value
        End Set
    End Property

    Private Sub ThisWorkbook_BeforeClose(ByRef cancel As Boolean) Handles Me.BeforeClose
        ' If the user tries to close a workbook with no data - bypass the save prompt.
        _silence = True
    End Sub

    ''' <summary>
    ''' Before the document is saved this event allows saving data related to the document.
    ''' </summary>
    ''' <param name="saveAsUI"></param>
    ''' <param name="cancel"></param>
    ''' <remarks>
    ''' Occurs when the user chooses to save, but before the actual save occurs.  In theory a user can
    ''' abort saving the document - after this call - but there is no other similar event that occurs 
    ''' in relation to saving the document which is after the point of no-return for the user's decision to
    ''' save the document.
    ''' </remarks>
    Private Sub ThisWorkbook_BeforeSave(ByVal saveAsUI As Boolean, ByRef cancel As Boolean) Handles Me.BeforeSave
        ' Time to save....
        If CompanyIDs.Count > 0 Then
            Dim forecastList = Globals.Sheet1.ListForecast.Range
            ' Remember it's VSTO it's 1 based!
            Dim forecastYear As Integer
            Dim forecastmonth As Integer
            Dim forecastSales As Decimal
            Dim companyNm As String
            If ForecastID = 0 Then
                'Insert a new Forecast.
                Dim newForecast = New Sales_Forecast With {.ForecastSalesPersonID = SalesEmployeeID, .ForecastTerritoryID = territoryID_backing}
                AdvWorksDC_backing.Sales_Forecast.InsertOnSubmit(newForecast)
                AdvWorksDC_backing.SubmitChanges()
                ' Get the forecast id and set it into the document cache so we can get to it later.

                Me.ForecastID = newForecast.ForecastID
                ' Now that we have the forecast ID we need to update the various forecast predictions for that id
                For row As Integer = 2 To 6
                    forecastYear = ForecastStartYear
                    forecastmonth = ForecastStartMonth
                    companyNm = CType(forecastList.Cells(row, 1), Microsoft.Office.Interop.Excel.Range).Value2.ToString()
                    If Not companyNm = "-" Then
                        For cell As Integer = 2 To 13
                            Dim curCell As Microsoft.Office.Interop.Excel.Range = CType(forecastList.Cells(row, cell), Microsoft.Office.Interop.Excel.Range)
                            forecastSales = CDec(curCell.Value2)

                            Dim newForcastEst = New Sales_ForecastEstimates With {.CompanyID = CompanyIDs(companyNm), _
                                    .Confidence = 100, .ForeCastID = ForecastID, .Month = forecastmonth, _
                                    .Year = forecastYear, .SalesForecast = forecastSales, .SalesPersonId = SalesEmployeeID}

                            AdvWorksDC_backing.Sales_ForecastEstimates.InsertOnSubmit(newForcastEst)
                            AdvWorksDC_backing.SubmitChanges()
                            forecastmonth += 1
                            If forecastmonth > 12 Then
                                forecastmonth = 1
                                forecastYear += 1
                            End If
                        Next
                    End If
                Next
                'Now disable the ability to change the companies.
                ActionsPaneForecast1.DeactivateControls()
            Else
                For row = 2 To 6
                    forecastYear = ForecastStartYear
                    forecastmonth = ForecastStartMonth
                    companyNm = CType(forecastList.Cells(row, 1), Microsoft.Office.Interop.Excel.Range).Value2.ToString()
                    If Not companyNm = "-" Then
                        For cell As Integer = 2 To 13
                            Dim curCell As Microsoft.Office.Interop.Excel.Range = CType(forecastList.Cells(row, cell), Microsoft.Office.Interop.Excel.Range)
                            forecastSales = CDec(curCell.Value2)

                            Dim updForecastEst = (From forecastEstTbl In AdvWorksDC_backing.Sales_ForecastEstimates _
                                                Select forecastEstTbl _
                                                Where (forecastEstTbl.CompanyID = CompanyIDs(companyNm) And _
                                                       forecastEstTbl.ForeCastID = Me.ForecastID And _
                                                       forecastEstTbl.SalesPersonId = SalesEmployeeID And _
                                                       forecastEstTbl.Month() = forecastmonth)).First
                            updForecastEst.SalesForecast = forecastSales
                            AdvWorksDC_backing.SubmitChanges()
                            forecastmonth += 1
                            If forecastmonth > 12 Then
                                forecastmonth = 1
                                forecastYear = ForecastStartYear + 1
                            End If
                        Next
                    End If
                Next

            End If
        Else
            If Not _silence Then
                MessageBox.Show("You have not entered any forecasts." & vbCrLf & "Please select 1 to 5 companies to forecast.")
            End If
            cancel = True
        End If
    End Sub

    ''' <summary>
    ''' Fires when the document is first opened.  Need to trigger the creation of the actions pane and trigger the tasks for the initial load.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        Me.ActionsPane.Controls.Add(ActionsPaneForecast1)
        ' Populate the placeholder info
        Dim salesRow() As Object = {"-", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
        For i = 0 To 4
            Globals.ThisWorkbook.salesDataTable_backing.LoadDataRow(CType(salesRow, Object()), False)
            Globals.ThisWorkbook.ForecastDataTable_backing.LoadDataRow(CType(salesRow, Object()), True)
            Globals.ThisWorkbook.blankDataTable.LoadDataRow(CType(salesRow, Object()), False)
        Next

        If SalesEmployeeID = 0 Then
            ' Get the date range
            ' Set the end date to the end of the last month.
            SalesStartDate = GetQuarterStart(Today)
            SalesEndDate = SalesStartDate.AddMonths(12)
            Globals.ThisWorkbook.ForecastStartYear = SalesEndDate.Year
            Globals.ThisWorkbook.ForecastStartMonth = SalesEndDate.Month
            ' This moves from the 1st day of first forecast month to _
            'the last day of the 12th month... regardless of days in that month.
            SalesEndDate = SalesEndDate.AddDays(-1)

            Dim curdate As Date = #1/1/2000#
            Dim curCell As Microsoft.Office.Interop.Excel.Range
            ' The simple part of this is we want the grid to show from the start of the next quarter so
            ' using the start date as the first month makes sense... remember everything in VSTO is 1
            ' based so we want the second column meaning add 2 to the zero based index.
            For i = 0 To 11
                curdate = SalesStartDate.AddMonths(i)
                curCell = CType(Globals.Sheet1.ListSales.HeaderRowRange.Cells(1, i + 2), Microsoft.Office.Interop.Excel.Range)
                curCell.Value2 = curdate.ToString("MMM-yy")
                curdate = curdate.AddYears(1)
                curCell = CType(Globals.Sheet1.ListForecast.HeaderRowRange.Cells(1, i + 2), Microsoft.Office.Interop.Excel.Range)
                curCell.Value2 = curdate.ToString("MMM-yy")
            Next

            Globals.Sheet1.ListSales.SetDataBinding(Globals.ThisWorkbook.salesDataTable_backing)
            Globals.Sheet1.ListForecast.DataSource = Globals.ThisWorkbook.ForecastDataTable_backing
        Else
            SalesStartDate = GetQuarterStart(forecastDate_backing)
            SalesEndDate = SalesStartDate.AddMonths(12)
            SalesEndDate = SalesEndDate.AddDays(-1)
        End If
        ActionsPaneForecast1.Startup()
    End Sub

    ''' <summary>
    ''' When the workbook is closed - clean up the data context.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub ThisWorkbook_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown
        Me.AdvWorksDC_backing.Dispose()
        Me.AdvWorksDC_backing = Nothing
    End Sub

    ''' <summary>
    ''' Returns the start date for sales data based on Quarter Starts
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>Forecasts should be for the future so set the start date based on the start of the next quarter,
    ''' Then set the date back one year to account for the fact that we want to get historical sales data to base
    ''' the estimate on.</remarks>
    Private Function GetQuarterStart(ByRef startDate As Date) As Date
        Dim stMonth As Int16 = CType(startDate.Month, Short)
        Select Case stMonth
            Case 1, 2, 3
                stMonth = 4
                Globals.Sheet1.Name = "FY" & Today.ToString("yy") & " Q4 - FY" & Today.AddYears(1).ToString("yy") & " Q3"
            Case 4, 5, 6
                stMonth = 7
                Globals.Sheet1.Name = "FY" & Today.ToString("yy") & " Q1 - FY" & Today.AddYears(1).ToString("yy") & " Q4"
            Case 7, 8, 9
                stMonth = 10
                Globals.Sheet1.Name = "FY" & Today.AddYears(1).ToString("yy") & " Q2 - FY" & Today.AddYears(2).ToString("yy") & " Q1"
            Case 10, 11, 12
                stMonth = 1
                Globals.Sheet1.Name = "FY" & Today.AddYears(1).ToString("yy") & " Q3 - FY" & Today.AddYears(2).ToString("yy") & " Q2"
            Case Else
                ' this should never happen...
                stMonth = 7
                Globals.Sheet1.Name = "FY" & Today.ToString("yy") & " Q1 - FY" & Today.AddYears(1).ToString("yy") & " Q4"
        End Select

        If stMonth < 3 Then
            Return Date.Parse(stMonth & "/01/" & Today.Year.ToString())
        End If
        Return Date.Parse(stMonth & "/01/" & (Today.Year - 1).ToString())
    End Function

End Class
